package com.library.dao;

import com.library.util.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.SQLException;

public class BookCopyDao {
    private final QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());

    // 查询副本状态
    public int getStatusByBarCode(String barCode) {
        String sql = "SELECT status FROM bookcopy WHERE barCode = ?";
        try {
            Integer status = queryRunner.query(sql, new ScalarHandler<Integer>(), barCode);
            return status != null ? status : -1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }
    // 更新副本状态
    public void updateStatus(String barCode, int status) {
        String sql = "UPDATE bookcopy SET status = ? WHERE barCode = ?";
        try {
            queryRunner.update(sql, status, barCode);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 动态分页查询图书副本信息
     * @param pageNum 页码（从1开始）
     * @param pageSize 每页显示数量
     * @param keyword 关键词（可为null）
     * @return 当前页的副本列表
     */
    public java.util.List<com.library.entity.po.BookCopyPO> pageBookcopy(int pageNum, int pageSize, String keyword) {
        int startIndex = (pageNum - 1) * pageSize;
        StringBuilder sql = new StringBuilder("select bc.*, b.bname from bookcopy bc left join book b on bc.ISBN = b.ISBN");
        java.util.List<Object> params = new java.util.ArrayList<>();
        if (keyword != null && !keyword.trim().isEmpty()) {
            sql.append(" where bc.ISBN like ? or bc.barCode like ? or b.bname like ?");
            params.add("%" + keyword + "%");
            params.add("%" + keyword + "%");
            params.add("%" + keyword + "%");
        }
        sql.append(" limit ?, ?");
        params.add(startIndex);
        params.add(pageSize);
        try {
            return queryRunner.query(sql.toString(), new org.apache.commons.dbutils.handlers.BeanListHandler<>(com.library.entity.po.BookCopyPO.class), params.toArray());
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return new java.util.ArrayList<>();
    }

    public Long countByISBN(String ISBN) {
        String sql = "select count(*) from bookcopy where ISBN = ? and status != 2";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.ScalarHandler<Long>(), ISBN);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }

    public Long countBorrowByISBN(String ISBN) {
        String sql = "select count(*) from bookcopy where ISBN = ? and status = 0";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.ScalarHandler<Long>(), ISBN);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }

    public Long countBookcopy(String keyword) {
        StringBuilder sql = new StringBuilder("select count(*) from bookcopy bc left join book b on bc.ISBN = b.ISBN");
        java.util.List<Object> params = new java.util.ArrayList<>();
        if (keyword != null && !keyword.trim().isEmpty()) {
            sql.append(" where bc.ISBN like ? or bc.barCode like ? or b.bname like ?");
            params.add("%" + keyword + "%");
            params.add("%" + keyword + "%");
            params.add("%" + keyword + "%");
        }
        try {
            return queryRunner.query(sql.toString(), new org.apache.commons.dbutils.handlers.ScalarHandler<Long>(), params.toArray());
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }

    public boolean isbarcodexists(String barcode) {
        String sql = "SELECT COUNT(*) FROM bookcopy WHERE barcode = ?";
        try {
            Long count = queryRunner.query(sql, new org.apache.commons.dbutils.handlers.ScalarHandler<Long>(), barcode);
            return count != null && count > 0;
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean addBookCopy(com.library.entity.Bookcopy bookCopy) {
        String sql = "insert into bookcopy (barCode, ISBN, place, status) values(?,?,?,?)";
        try {
            int result = queryRunner.update(sql, bookCopy.getBarCode(), bookCopy.getISBN(),
                    bookCopy.getPlace(), bookCopy.getStatus());
            return result > 0;
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean updateBookCopy(String barCode, String place, Integer status) {
        String sql = "update bookcopy set place = ?, status = ? where barCode = ?";
        try {
            int result = queryRunner.update(sql, place, status, barCode);
            return result > 0;
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean updateBookCopyStatus(String barCode, Integer status) {
        String sql = "update bookcopy set status = ? where barCode = ?";
        try {
            int result = queryRunner.update(sql, status, barCode);
            return result > 0;
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean batchUpdateBookCopyStatus(String[] barCodes, Integer status) {
        if (barCodes == null || barCodes.length == 0) {
            return false;
        }
        StringBuilder sql = new StringBuilder("update bookcopy set status = ? where barCode in (");
        for (int i = 0; i < barCodes.length; i++) {
            if (i > 0) sql.append(",");
            sql.append("?");
        }
        sql.append(") and status != 0");
        try {
            Object[] params = new Object[barCodes.length + 1];
            params[0] = status;
            System.arraycopy(barCodes, 0, params, 1, barCodes.length);
            int result = queryRunner.update(sql.toString(), params);
            return result > 0;
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public com.library.entity.Bookcopy getBookCopyByBarCode(String barCode) {
        String sql = "select * from bookcopy where barCode = ?";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.BeanHandler<>(com.library.entity.Bookcopy.class), barCode);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public com.library.entity.po.BookCopyPO getBookCopyPOByBarCode(String barCode) {
        String sql = "select bc.*, b.bname from bookcopy bc left join book b on bc.ISBN = b.ISBN where bc.barCode = ?";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.BeanHandler<>(com.library.entity.po.BookCopyPO.class), barCode);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean deleteBookCopy(String barCode) {
        String sql = "delete from bookcopy where barCode = ?";
        try {
            int result = queryRunner.update(sql, barCode);
            return result > 0;
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public java.util.List<com.library.entity.Bookcopy> getBookCopiesByISBN(String isbn) {
        String sql = "select * from bookcopy where ISBN = ? order by barCode";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.BeanListHandler<>(com.library.entity.Bookcopy.class), isbn);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return new java.util.ArrayList<>();
    }

    public Long countBookCopyByStatus(Integer status) {
        String sql = "select count(*) from bookcopy where status = ?";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.ScalarHandler<Long>(), status);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }
} 